package com.pan.studentmaster;

import android.util.Log;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class DB extends SQLiteOpenHelper {

	public static abstract class STUDENT implements BaseColumns {
		public static final String _T			= "STUDENT";
		public static final String _SCHOOL		= "_school";
		public static final String _GRADE		= "_grade";
		public static final String _CLASS		= "_class";
		public static final String _NUMBER		= "_number";
		public static final String _NAME		= "_name";
	}
	
	public static abstract class COURSE implements BaseColumns {
		public static final String _T			= "COURSE";
		public static final String _NAME		= "_name";
	}
	
	public static abstract class BOOK implements BaseColumns {
		public static final String _T			= "BOOK";
		public static final String _COURSE		= "_course";
		public static final String _BOOKTYPE	= "_booktype";
		public static final String _TITLE		= "_title";
		public static final String _SUBTITLE	= "_subtitle";
		public static final String _PAGES		= "_pages";
		public static final String _PAGE		= "_page";
	}
	
	public static final int			BOOK_TEXT	= 1;
	public static final int			BOOK_BLANK	= 2;
	public static final int			BOOK_LINE	= 3;
	public static final int			BOOK_SBLOCK	= 4;
	public static final int			BOOK_BBLOCK	= 5;

	static public DB instance() {
		if(_instance == null)
			_instance = new DB(Main.instance());
		return _instance;
	}
	
	public Cursor getStudent() {
		String[] columns = new String[] {
			STUDENT._ID,
			STUDENT._SCHOOL,
			STUDENT._GRADE,
			STUDENT._CLASS,
			STUDENT._NUMBER,
			STUDENT._NAME };
		Cursor cursor = getReadableDatabase().query(STUDENT._T, columns, null, null, null, null, null);
		cursor.moveToFirst();
		return cursor;
	}
	
	public String getCourseName(int courseId) {
		String[] columns = new String[] { COURSE._NAME };
		String where = COURSE._ID + " = " + courseId;
		Cursor c = getReadableDatabase().query(COURSE._T, columns, where, null, null, null, null);
		c.moveToFirst();
		String s = c.getString(c.getColumnIndex(COURSE._NAME));
		c.close();
		return s;
	}
	
	public Cursor getCourses() {
		String[] columns = new String[] {
			COURSE._ID,
			COURSE._NAME };
		Cursor cursor = getReadableDatabase().query(COURSE._T, columns, null, null, null, null, null);
		cursor.moveToFirst();
		return cursor;
	}
	
	public Cursor getBooks(int course) {
		String[] cs = new String[] { BOOK._ID, BOOK._BOOKTYPE, BOOK._TITLE, BOOK._SUBTITLE };
		String where = null;
		if(course >= 0)
			where = BOOK._COURSE + " = " + course;
		String orderBy = BOOK._BOOKTYPE;
		Cursor cursor = getReadableDatabase().query(BOOK._T, cs, where, null, null, null, orderBy);
		cursor.moveToFirst();
		return cursor;
	}
	
	public Cursor getBookInfo(int bookId) {
		String[] cs = new String[] { BOOK._BOOKTYPE, BOOK._TITLE, BOOK._SUBTITLE, BOOK._PAGE, BOOK._PAGES };
		String where = BOOK._ID + " = " + bookId;
		Cursor c = getReadableDatabase().query(BOOK._T, cs, where, null, null, null, null);
		c.moveToFirst();
		return c;
	}
	
	public void setBookPage(int bookId, int pageId) {
		ContentValues cv = new ContentValues();
		cv.put(BOOK._PAGE, pageId);
		String where = BOOK._ID + " = " + bookId;
		getWritableDatabase().update(BOOK._T, cv, where, null);
	}
	
	// private
	
	static private DB				_instance	= null;
	static private final int		_version 	= 1;
	static private final String		_name 		= "student";
	
	private DB(Context context) {
		super(context, _name, null, _version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String s = "CREATE TABLE " + STUDENT._T + " ( "
			+ STUDENT._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ STUDENT._SCHOOL + " TEXT, "
			+ STUDENT._GRADE + " INTEGER, "
			+ STUDENT._CLASS + " TEXT, "
			+ STUDENT._NUMBER + " INTEGER, "
			+ STUDENT._NAME + " TEXT ) ";
		Log.i("DB", s);
		db.execSQL(s);
		s = "CREATE TABLE " + COURSE._T + " ( "
			+ COURSE._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ COURSE._NAME + " TEXT ) ";
		Log.i("DB", s);
		db.execSQL(s);
		s = "CREATE TABLE " + BOOK._T + " ( "
			+ BOOK._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ BOOK._COURSE + " INTEGER, "
			+ BOOK._BOOKTYPE + " INTEGER, "
			+ BOOK._TITLE + " TEXT, "
			+ BOOK._SUBTITLE + " TEXT, "
			+ BOOK._PAGES + " INTEGER, "
			+ BOOK._PAGE + " INTEGER ) ";
		Log.i("DB", s);
		db.execSQL(s);
		s = "CREATE INDEX " + BOOK._T + "_ui ON " + BOOK._T + " ( " + BOOK._COURSE + " ASC ) ";
		Log.i("DB", s);
		db.execSQL(s);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("DB", "Upgrade from " + oldVersion + " to " + newVersion);
		String s = "DROP TABLE IF EXISTS " + STUDENT._T;
		Log.i("DB", s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + COURSE._T;
		Log.i("DB", s);
		db.execSQL(s);
		s = "DROP TABLE IF EXISTS " + BOOK._T;
		Log.i("DB", s);
		db.execSQL(s);
		onCreate(db);
	}

}
